/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo.DAL;

import Modelo.Implemento;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import org.json.JSONObject;
import org.json.JSONArray;

/**
 *
 * @author h-m
 */
public class Implemento_DAO {

    public Implemento_DAO() {
    }

    public static JSONArray consultarImplemento(String query) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("¡Error Consultando Implementos!");
            return null;
        }
        System.out.println(query);
        try {
            rs = s.executeQuery(query);

            JSONArray implementos = new JSONArray();
            try {
                int i = 0;
                while (rs.next()) {
                    JSONObject implemento = new JSONObject();
                    implemento.put("id_implemento", rs.getString(1));
                    implemento.put("tipo", rs.getString(9));
                    implemento.put("subtipo", rs.getString(10));
                    implemento.put("marca", rs.getString(8));
                    implemento.put("status", rs.getString(2));
                    implemento.put("duracion_estimada", rs.getString(3));
                    implemento.put("duracion_real", rs.getString(4));
                    implemento.put("calificacion", rs.getString(5));
                    implemento.put("caracteristicas_adicionales", rs.getString(6));
                    implemento.put("frecuencia_de_uso", rs.getString(7));
                    implemento.put("tam", rs.getString(11));
                    implementos.put(i++, implemento);
                }
            } catch (Exception e) {
                System.out.println("Error obteniendo data de implementos.");
            }
            return implementos;
        } catch (SQLException se) {
            se.printStackTrace();
            System.out.println("Error Consultado el implemento");
            return null;
        }
    }

    public boolean insertarImplemento(Implemento imp) {

        String query = "INSERT INTO implemento(status, duracion_estimada,"
                + " duracion_real, calificacion, caracteristicas_adicionales, frecuencia_de_uso, marca, nombre, subtipo)"
                + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        ConexionBD conexion = new ConexionBD();
        PreparedStatement s = null;

        try {
            s = conexion.session.prepareStatement(query);
            s.setString(1, imp.getStatus());
            s.setString(2, imp.getDuracionEstimada());
            s.setString(3, imp.getDuracionReal());
            s.setInt(4, imp.getCalificacion());
            s.setString(5, imp.getCaracteristicasAdicionales());
            s.setString(6, imp.getFrecuenciaDeUso());
            s.setString(7, imp.getMarca());
            s.setString(8, imp.getNombre());
            s.setString(9, imp.getSubtipo());
            System.out.println(s);
            int retorno = s.executeUpdate();
            System.out.println(retorno);
            return true;

        } catch (SQLException sqle) {
            System.out.println("Error Agregando el Implemento!");
            System.out.println("SQLState: "
                    + sqle.getSQLState());
            System.out.println("SQLErrorCode: "
                    + sqle.getErrorCode());
            sqle.printStackTrace();

            return false;
        }
    }

    public static boolean actualizarImplemento(Implemento i) {

        String query = "UPDATE implemento set  status = ?,"
                + " duracion_estimada = ?, duracion_real = ?, calificacion = ?,"
                + " caracteristicas_adicionales = ?, frecuencia_de_uso = ?,"
                + " marca = ?, nombre = ?, subtipo = ?"
                + " where id_implemento = " + i.getId_implemento() + ";";

        ConexionBD conexion = new ConexionBD();
        PreparedStatement s = null;

        try {
            s = conexion.session.prepareStatement(query);
            s.setString(1, i.getStatus());
            s.setString(2, i.getDuracionEstimada());
            s.setString(3, i.getDuracionReal());
            s.setInt(4, i.getCalificacion());
            s.setString(5, i.getCaracteristicasAdicionales());
            s.setString(6, i.getFrecuenciaDeUso());
            s.setString(7, i.getMarca());
            s.setString(8, i.getNombre());
            s.setString(9, i.getSubtipo());
            System.out.println(s);
            int retorno = s.executeUpdate();
            System.out.println(retorno);
            return true;

        } catch (SQLException sqle) {
            System.out.println("Error Actualizando el Implemento!");
            System.out.println("SQLState: "
                    + sqle.getSQLState());
            System.out.println("SQLErrorCode: "
                    + sqle.getErrorCode());
            sqle.printStackTrace();

            return false;
        }
    }

        ////////////// Modulo 4: Asignar Implementos de Guardia /////////

    public JSONObject consultarTipoImplementoG(){
        ConexionBD conex = new ConexionBD();
        JSONObject tipoImplementos = new JSONObject();
        try{
            Statement st = conex.session.createStatement();
            String sql;
            sql = "select distinct nombre from tipo_implemento";
            ResultSet rs = st.executeQuery(sql);
            int i = 0;
            while(rs.next()){
                JSONObject tipoImpl = new JSONObject();
                tipoImpl.put("nombre",rs.getString(1));
                tipoImplementos.put(""+i++,tipoImpl);
            }
        } catch (Exception e){
            System.out.println("Error consultando los tipos de implementos\n"+
                    e.getMessage());
        }
        return tipoImplementos;
    }

    public JSONObject consultarSubtipoImplementoG(String tipo){
        ConexionBD conex = new ConexionBD();
        JSONObject subtipoImplementos = new JSONObject();
        try{
            Statement st = conex.session.createStatement();
            String sql;
            sql = "select distinct subtipo from tipo_implemento "
                    + "where nombre = '"+tipo+"'";
            ResultSet rs = st.executeQuery(sql);
            int i = 0;
            while(rs.next()){
                JSONObject subtipoImpl = new JSONObject();
                subtipoImpl.put("subtipo",rs.getString(1));
                subtipoImplementos.put(""+i++,subtipoImpl);
            }
        } catch (Exception e){
            System.out.println("Error consultando los subtipos de implementos\n"+
                    e.getMessage());
        }
        return subtipoImplementos;
    }

    public JSONObject detallesImplementos(String tipo, String subtipo){

        String query = "select * from implemento where nombre = '"+tipo+"' and "
                + "subtipo = '"+subtipo+"'";
        ConexionBD conexion = new ConexionBD();
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("¡Error Consultando Implementos!\n"+
                    se.getMessage());
            return null;
        }
        System.out.println("\n\nConsulta Implementos: "+query);
        try {
            rs = s.executeQuery(query);

            JSONObject implementos = new JSONObject();
            try {
                int i = 0;
                while (rs.next()) {
                    JSONObject implemento = new JSONObject();
                    //implemento.put("tipo", rs.getString(9));
                    //implemento.put("subtipo", rs.getString(10));
                    implemento.put("id_implemento", rs.getLong("id_implemento"));
                    implemento.put("marca", rs.getString("marca"));
                    implemento.put("status", rs.getString("status"));
                    implemento.put("duracion_estimada", rs.getString("duracion_estimada"));
                    implemento.put("duracion_real", rs.getString("duracion_real"));
                    implemento.put("calificacion", rs.getString("calificacion"));
                    implemento.put("caracteristicas_adicionales", rs.getString("caracteristicas_adicionales"));
                    implemento.put("frecuencia_de_uso", rs.getString("frecuencia_de_uso"));
                    //implemento.put("tam", rs.getString(11));
                    implementos.put("" + i++, implemento);
                }
            } catch (Exception e) {
                System.out.println("Error obteniendo data de implementos.");
            }
            return implementos;
        } catch (SQLException se) {
            System.out.println("Error consultando los implementos\n"+
                    se.getMessage());
            return null;
        }
    }

    public boolean asignarImplemento(String ci, Long id_guardia,Long id_impl){
        ConexionBD conexion = new ConexionBD();
        //boolean status = false;
        if (conexion.session == null)
            return false;
        Statement stt = null;
        try{
            stt = conexion.session.createStatement();

            String query = "insert into se_asigna_implemento values "
                    + "('"+ci+"', "+id_guardia+", "+id_impl+")";
            System.out.println(query);
            stt.execute(query);
            return true;
        } catch(SQLException se){
            System.out.println("Error al insertar una asistencia.\n"+
                    se.getMessage());
            return false;
        } finally {
            if (conexion.session !=null)
                conexion.cerrarConexion();
        }
    }

    public boolean haSidoAsignadoImplemento(Long id_impl,String fecha_guardia){
        ConexionBD conex = new ConexionBD();
        int numAsig = 0;

        try{
            Statement st = conex.session.createStatement();
            String sql;
            /*sql = "select * from se_asigna_implemento where "
                    + "id_guardia ="+id_guardia+" and "
                    + "id_implemento="+id_impl*/

            sql = "select * from se_asigna_implemento sai where "
                    + "sai.id_implemento="+id_impl+" and sai.id_guardia in "
                    + "(select g.id_guardia from guardia g where "
                    + "g.fecha_guardia = '"+fecha_guardia+"')";
            ResultSet rs = st.executeQuery(sql);

            while(rs.next()){

                numAsig++;
            }
        } catch (Exception e){
            System.out.println("Error al consultar si el implementa ya fue asignado\n"+
                    e.getMessage());
        }
        return numAsig>0;
    }

}


